Skip Headers

Oracle® OLAP Reference 10g Release 2 (10.2) Part Number B14350-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Creating and Refreshing a Workspace Cube

Once you have defined a cube in the OLAP Catalog for your star schema, you can create the cube in the analytic workspace.

You must call `CREATE_AWDIMENSION` to create each of the cube's dimensions before calling `CREATE_AWCUBE` to create the cube. To populate the cube, you must call `REFRESH_AWDIMENSION` to populate each of the cube's dimensions before calling `REFRESH_AWCUBE` to refresh the cube's measures.

Within an analytic workspace, dimensions can be shared by more than one cube. When creating a new workspace cube, you will only call `CREATE_AWDIMENSION` for OLAP Catalog dimensions that have not been used as the source for dimensions of cubes that already exist in the workspace.

Note: `CREATE_AWCUBE` opens the analytic workspace with read/write access. It updates the workspace, but it does not execute a SQL `COMMIT`. The analytic workspace must already exist before you call `CREATE_AWCUBE` or any other procedures in the `DBMS_AWM` package.

Example 1-2 shows the procedure calls for creating and populating the `XADEMO.ANALYTIC_CUBE` cube in an analytic workspace.

Example 1-2 Creating the ANALYTIC_CUBE Cube in an Analytic Workspace

    --- SET UP
    set serveroutput on
    execute cwm2_olap_manager.set_echo_on;

    create or replace directory myscripts as '/users/myxademo/myscripts';
    execute cwm2_olap_manager.begin_log('MYSCRIPTS' , 'anacube.log');

    --- CREATE THE ANALYTIC WORKSPACE
    execute dbms_aw.execute ('aw create ''myaw''');

    --- CREATE AND REFRESH THE DIMENSIONS
    execute dbms_awm.create_awdimension
              ('XADEMO','CHANNEL','MYSCHEMA', 'MYAW', 'AW_CHAN');
    execute dbms_awm.create_awdimension
              ('XADEMO','GEOGRAPHY','MYSCHEMA','MYAW', 'AW_GEOG');
    execute dbms_awm.create_awdimension
              ('XADEMO','PRODUCT','MYSCHEMA', 'MYAW', 'AW_PROD');
    execute dbms_awm.create_awdimension
              ('XADEMO','TIME','MYSCHEMA', 'MYAW', 'AW_TIME');
    execute dbms_awm.refresh_awdimension
              ('MYSCHEMA', 'MYAW', 'AW_CHAN');
    execute dbms_awm.refresh_awdimension
              ('MYSCHEMA', 'MYAW', 'AW_PROD');
    execute dbms_awm.refresh_awdimension
              ('MYSCHEMA', 'MYAW', 'AW_GEOG');
    execute dbms_awm.refresh_awdimension
              ('MYSCHEMA', 'MYAW', 'AW_TIME');

    --- CREATE AND REFRESH THE CUBE
    execute dbms_awm.create_awcube
              ('XADEMO', 'ANALYTIC_CUBE','MYSCHEMA', 'MYAW','AW_ANACUBE');
    execute dbms_awm.create_awcubeload_spec
              ('AC_CUBELOADSPEC', 'XADEMO', 'ANALYTIC_CUBE', 'LOAD_DATA');
    execute dbms_awm.refresh_awcube
              ('MYSCHEMA', 'MYAW', 'AW_ANACUBE', 'AC_CUBELOADSPEC');

    --- COMMIT AND WRAP UP
    commit;
    execute cwm2_olap_manager.set_echo_off;
    execute cwm2_olap_manager.end_log

When you query the Active Catalog view `ALL_OLAP2_AW_CUBES` , you will see the following row.

    AW_OWNER    AW_NAME     AW_LOGICAL_NAME       SOURCE_OWNER  SOURCE_NAME
    ----------  ----------  -------------------  -------------  -------------
    MYSCHEMA    MYAW        AW_ANACUBE            XADEMO        ANALYTIC_CUBE

The measures in the source fact table may have numeric, text, or date data types. When `REFRESH_AWCUBE` loads the data into a workspace cube, it converts the RDBMS data types to types that are native to analytic workspaces. The data type conversion is described in Table 1-8.

If a source measure has a data type not described in Table 1-8, the measure is ignored by `REFRESH_AWCUBE` and none of its data or metadata is loaded into the analytic workspace.

Table 1-8 Conversion of RDBMS Data Types to Workspace Data Types

RDBMS Data Type | Analytic Workspace Data Type
--- | ---
`NUMBER` | `DECIMAL`
`CHAR`, `LONG`, `VARCHAR`, `VARCHAR2` | `TEXT`
`NCHAR`, `NVARCHAR2` | `NTEXT`
`DATE` | `DATE`

`CREATE_AWCUBE` ensures that the generic standard form objects that support cubes exist in the workspace, and it registers the specified cube in the workspace. However, the metadata that defines the logical structure of this particular cube is not instantiated in the workspace until you call `REFRESH_AWCUBE`.

For example, if you have just created a cube `AW_ANACUBE` in a workspace `MYAW` in `MYSCHEMA` from the source cube `XADEMO`.`ANALYTIC_CUBE`, you can query the Active Catalog to check the workspace.

    SQL>select * from ALL_OLAP2_AW_CUBES where AW_LOGICAL_NAME in 'AW_ANACUBE';

    AW_OWNER   AW_NAME     AW_LOGICAL_NAME       SOURCE_OWNER    SOURCE_NAME
    ---------- ----------- --------------------- --------------- --------------
    MYSCHEMA   MYAW        AW_ANACUBE            XADEMO          ANALYTIC_CUBE

The following query shows that there are no measures associated with the cube. The measures, dimensions, and descriptions will be instantiated when the cube is refreshed.

    SQL>select * from ALL_OLAP2_AW_CUBE_MEASURES where AW_CUBE_NAME in 'AW_ANACUBE';

    no rows selected

You should refresh a cube whenever changes occur in the source fact table. These changes could be additions or deletions of data, for example updating sales figures, or they could be changes to the cube's metadata, such as adding a measure or renaming a description.

When you refresh a cube, you must first refresh any of its dimensions that have changed. If you want to drop or add a dimension to a cube, you must drop the cube and re-create it.

Every time you refresh a cube that has an associated aggregation specification, you must reaggregate the cube.

If you make changes to the composite specification associated with a cube, you must drop the cube and re-create it in the analytic workspace. You cannot refresh a cube with a modified composite specification.